
/*
 * Created by Mallik Ankati 
 * Date : 11/23/2004
 * Name : CustomerRelationshipDAO.java
 * 
 */
 
package com.scalar.chiptrack.sales.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.sales.CustomerRelationship;

public class CustomerRelationshipDAO{

	public static ArrayList getParentChildRelationshipInformation(String customerId) throws ScalarException
	{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "select C.c_name as ParentCustomer_Name, Customer_Parent_Child_Relationship.ParentCustomer_ID,  Customer_Parent_Child_Relationship.ChildCustomer_ID, CC.c_name as ChildCustomer_Name from Customer C, Customer CC, Customer_Parent_Child_Relationship where Customer_Parent_Child_Relationship.ParentCustomer_ID = C.Customer_ID AND Customer_Parent_Child_Relationship.ChildCustomer_ID = CC.Customer_ID"  );
	
	            rs = pstmt.executeQuery ();
	            while ( rs.next () )
	            {
	             	CustomerRelationship cr = new CustomerRelationship(rs.getString("ParentCustomer_Name"),
	             														rs.getString("ParentCustomer_ID"),
	             														rs.getString("ChildCustomer_Name"),
	             														rs.getString("ChildCustomer_ID"));
					list.add(cr);
	            }
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not get Parent/Child relation info from database", sqlex );
	        }
	        finally
	        {
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        return list;
	}      
	      
	public static ArrayList getODMRelationshipInformation(String customerId) throws ScalarException
	{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "select  cor.ParentCustomer_ID, C.C_name as ParentCustomer_Name, cor.ODMCustomer_ID, CC.C_Name as ODM_Name from Customer_odm_relationship cor, Customer C, Customer CC, Customer_Relationship_Status crs where CC.C_Disabled = 1 and cor.ParentCustomer_ID =? and cor.RelationshipStatus_ID = crs.RelationshipStatus_ID and crs.Name='Active' and cor.ParentCustomer_ID = C.Customer_ID and cor.ODMCustomer_ID = CC.Customer_ID ORDER BY CC.C_Name"  );
	            pstmt.setString(1, customerId);
	
	            rs = pstmt.executeQuery ();
	            while ( rs.next () )
	            {
	             	CustomerRelationship cr = new CustomerRelationship(rs.getString("ParentCustomer_Name"),
	             														rs.getString("ParentCustomer_ID"),
	             														rs.getString("ODM_Name"),
	             														rs.getString("ODMCustomer_ID"));
					list.add(cr);
	            }
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not get ODM relationship info from database", sqlex );
	        }
	        finally
	        {
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        return list;
	}
	      
	public static ArrayList getEndCustomerRelationshipInformation(String customerId) throws ScalarException
	{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "select cer.ParentCustomer_ID, C.C_name as ParentCustomer_Name, cer.EndCustomer_ID, CC.C_Name as Endcustomer from Customer_endcustomer_relationship cer, Customer_Relationship_Status crs , Customer C, Customer CC where CC.C_Disabled = 1 and cer.ParentCustomer_ID = ? and  cer.RelationshipStatus_ID = crs.RelationshipStatus_ID and crs.Name='Active' and cer.ParentCustomer_ID = C.Customer_ID and cer.EndCustomer_ID = CC.Customer_ID ORDER BY CC.C_Name"  );
				pstmt.setString(1, customerId);
	            rs = pstmt.executeQuery ();
	            while ( rs.next () )
	            {
	             	CustomerRelationship cr = new CustomerRelationship(rs.getString("ParentCustomer_Name"),
	             														rs.getString("ParentCustomer_ID"),
	             														rs.getString("Endcustomer"),
	             														rs.getString("EndCustomer_ID"));
					list.add(cr);
	            }
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not get End Customer Info from database", sqlex );
	        }
	        finally
	        {
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return list;
	    }
	      
		public static ArrayList getAllOdmList(String customerId) throws ScalarException
		{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_get_odm_list(?)}"  );
	            pstmt.setString(1, customerId);
	
	            rs = pstmt.executeQuery ();
	            
	            while ( rs.next () )
	            {
	             	CustomerRelationship cr = new CustomerRelationship("",
	             														"",
	             														rs.getString("Customer_Name"),
	             														rs.getString("Customer_ID"),
	             														rs.getString("Flag"));
					list.add(cr);
	            }
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not get ODM List from database", sqlex );
	        }
	        finally
	        {
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return list;
	      }
	      
	    public static boolean saveOdmList(String customerId, String[] odmId) throws ScalarException
		{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	        boolean success = false;
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_save_odm_list(?, ?)}" );
	            
	            if(null != odmId){
	            	//System.out.println("CustomerRelationshipDAO.saveOdmList()");
	            	for(int i=0; i<odmId.length; i++){
	            		
	            		pstmt.setString(1, customerId);
	            		pstmt.setString(2, odmId[i]);
	            		
	            		pstmt.addBatch();
	            	}
	            	
	            	pstmt.executeBatch();
	            	
	            }else{
	            	success = true;
	            }
	            
	            success = true;
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not save ODM List from database", sqlex );
	        }
	        finally
	        {
	        	if (conn != null) {
	        		try{
                  	if (success == true)
                    	conn.commit();
                    else
                    	conn.rollback();
                    }catch(SQLException sqle){
                    	sqle.printStackTrace ();
	            		throw new ScalarException ( "Could not Commit ODM List into database", sqle );
                    }
                }
                
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return success;
	    }
	    
	    
	    public static boolean deactivateOdmList(String customerId) throws ScalarException
		{
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	        boolean success = false;
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_deactivate_odm_list(?)}" );
	            pstmt.setString(1, customerId);
	            pstmt.executeUpdate();
	            
	            success = true;
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not  deactivate ODM List from database", sqlex );
	        }
	        finally
	        {
	        	if (conn != null) {
	        		try{
                  	if (success == true)
                    	conn.commit();
                    else
                    	conn.rollback();
                    }catch(SQLException sqle){
                    	sqle.printStackTrace ();
	            		throw new ScalarException ( "Could not Commit ODM List into database", sqle );
                    }
                }
                
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return success;
	      }
	      
        public static ArrayList getAllEndCustomerList(String customerId) throws ScalarException
		{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_get_endcustomer_list(?)}"  );
	            pstmt.setString(1, customerId);
	
	            rs = pstmt.executeQuery ();
	            
	            while ( rs.next () )
	            {
	             	CustomerRelationship cr = new CustomerRelationship("", "", rs.getString("Customer_Name"), rs.getString("Customer_ID"), rs.getString("Flag") );
					list.add(cr);
	            }
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not get End List from database", sqlex );
	        }
	        finally
	        {
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return list;
	      }
	      
	    public static boolean saveEndCustomerList(String customerId, String[] odmId) throws ScalarException
		{
	
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	        boolean success = false;
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_save_endcustomer_list(?, ?)}" );
	            
	            if(null != odmId){
	            	//System.out.println("CustomerRelationshipDAO.saveOdmList()");
	            	for(int i=0; i<odmId.length; i++){
	            		
	            		pstmt.setString(1, customerId);
	            		pstmt.setString(2, odmId[i]);
	            		
	            		pstmt.addBatch();
	            	}
	            	
	            	pstmt.executeBatch();
	            	
	            }else{
	            	success = true;
	            }
	            
	            success = true;
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not save Endcustomer List from database", sqlex );
	        }
	        finally
	        {
	        	if (conn != null) {
	        		try{
                  	if (success == true)
                    	conn.commit();
                    else
                    	conn.rollback();
                    }catch(SQLException sqle){
                    	sqle.printStackTrace ();
	            		throw new ScalarException ( "Could not Commit Endcustomer List into database", sqle );
                    }
                }
                
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return success;
	    }
	    
	    public static boolean deactivateEndCustomerList(String customerId) throws ScalarException
		{
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        ArrayList list = new ArrayList ();
	        boolean success = false;
	
	        try
	        {
	            conn = DataSource.getConnection ();
	            pstmt = conn.prepareStatement ( "{call sp_deactivate_endcustomer_list(?)}" );
	            pstmt.setString(1, customerId);
	            pstmt.executeUpdate();
	            
	            success = true;
	        }
	        catch ( Exception sqlex )
	        {
	            sqlex.printStackTrace ();
	            throw new ScalarException ( "Could not  deactivate Endcustomer List from database", sqlex );
	        }
	        finally
	        {
	        	if (conn != null) {
	        		try{
                  	if (success == true)
                    	conn.commit();
                    else
                    	conn.rollback();
                    }catch(SQLException sqle){
                    	sqle.printStackTrace ();
	            		throw new ScalarException ( "Could not Commit Endcustomer List into database", sqle );
                    }
                }
                
	            if ( rs != null )
	            {
	                try
	                {
	                    rs.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( pstmt != null )
	            {
	                try
	                {
	                    pstmt.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	            if ( conn != null )
	            {
	                try
	                {
	                    conn.close ();
	                }
	                catch ( SQLException excp )
	                {
	                }
	            }
	        }
	        
	        return success;
	      }
    public static ArrayList getAllOdmsForCustomer ( String customerId ) throws ScalarException {

        ArrayList list = new ArrayList();
        Iterator odmList = getAllOdmList( customerId ).iterator();
        try
        {
           while ( odmList.hasNext() ) {
                CustomerRelationship customerRelationship = (CustomerRelationship) odmList.next();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) ) {
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName(), customerRelationship.getChildCustomerId() ));
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }
    public static ArrayList getAllOdmsAndEndCustomers ( String customerId ) throws ScalarException {

        ArrayList list = new ArrayList();
        Iterator odmList = getAllOdmList( customerId ).iterator();
        Iterator endCustomerList = getAllEndCustomerList(customerId).iterator();
        try
        {
           while ( odmList.hasNext() ) {
                CustomerRelationship customerRelationship = (CustomerRelationship) odmList.next();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) ) {
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName()+"/"+"ODM", customerRelationship.getChildCustomerId() ));
                }
           }
           while ( endCustomerList.hasNext() ) {
                CustomerRelationship customerRelationship = (CustomerRelationship) endCustomerList.next();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) ) {
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName()+"/"+"End Customer", customerRelationship.getChildCustomerId() ));
                }
           }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }
    public static ArrayList getOdmsForChangeSalesOrder ( String customerId, String odmId ) throws ScalarException
    {
        String childCustomerId = null;
        boolean found = false;
        ArrayList list = new ArrayList();
        Iterator odmList = getAllOdmList( customerId ).iterator();
        try
        {
           while ( odmList.hasNext() ) {
                CustomerRelationship customerRelationship = (CustomerRelationship) odmList.next();
                childCustomerId = customerRelationship.getChildCustomerId();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) ) {
                    if ( odmId.equalsIgnoreCase( childCustomerId ) ) {
                        found = true;
                    }
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName(), childCustomerId ));
                }
            }
            if ( !found && Integer.parseInt( odmId ) > 0 ) {
                list.add( new LabelValueBean( CustomerDAO.getCustomerName( null, Integer.parseInt(odmId ) ), odmId ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList getAllEndCustomersForCustomer ( String customerId ) throws ScalarException
    {
        ArrayList list              = new ArrayList();
        Iterator endCustomerList    = getAllEndCustomerList( customerId ).iterator();
        CustomerRelationship customerRelationship = null;
        try
        {
            while ( endCustomerList.hasNext() )
            {
                customerRelationship = ( CustomerRelationship ) endCustomerList.next();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) )
                {
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName(), customerRelationship.getChildCustomerId() ) );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList getEndCustomersForChangeSalesOrder ( String customerId, String endCustomerId ) throws ScalarException
    {
        String childCustomerId = null;
        boolean found = false;
        ArrayList list = new ArrayList();
        Iterator endCustomerList = getAllEndCustomerList( customerId ).iterator();
        try
        {
            while ( endCustomerList.hasNext() )
            {
                CustomerRelationship customerRelationship = ( CustomerRelationship ) endCustomerList.next();
                childCustomerId = customerRelationship.getChildCustomerId();
                if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) )
                {
                    if ( endCustomerId.equalsIgnoreCase( childCustomerId ) )
                    {
                        found = true;
                    }
                    list.add( new LabelValueBean( customerRelationship.getChildCustomerName(), childCustomerId ));
                }
            }
            if ( !found && Integer.parseInt( endCustomerId ) > 0 )
            {
                list.add( new LabelValueBean( CustomerDAO.getCustomerName( null, Integer.parseInt( endCustomerId ) ), endCustomerId ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }
}